package com.supermap.dataservice.agrinfo.dao;

import com.supermap.dataservice.util.Table;
import com.supermap.dubbo.api.agrinfo.entity.AgrAllInfo;
import com.supermap.dubbo.api.agrinfo.entity.Agrinfo;
import com.supermap.dubbo.api.agrinfo.entity.AgrinfoComment;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @Author:langhongshuai
 * @Description:农情数据访问层
 * @Date:18/1/29 下午2:42
 */
@Mapper
public interface AgrinfoDao {


    /**
     * 查询多少个区县
     * **/
    @SelectProvider(type = AgrinfoSql.class,method = "areaCountSql")
    int countCity(@Param("areacode") String areacode);

    /**
     * 按照角色统计农田
     * **/
    @SelectProvider(type = AgrinfoSql.class,method = "roleFarmCountSQL")
    List<Agrinfo> roleFarmCount(@Param("table") Table table, @Param("agrinfo") Agrinfo agrinfo);
    /***
     * 农田统计
     * ****/
    @SelectProvider(type = AgrinfoSql.class, method = "farmCountSQL")
    List<Agrinfo> farmCount(@Param("table") Table table, @Param("agrinfo") Agrinfo agrinfo);

    /****
     * 统计农情,灾情，咨询
     * ***/
    @SelectProvider(type = AgrinfoSql.class, method = "agriInfoCountSQL")
    public List<Agrinfo> agriInfoCount(@Param("table") Table table, @Param("agrinfo") Agrinfo agrinfo);

    /**
     * 获得用户的所有农情信息
     *
     * @return
     */
    @Select("select t3.* ,t4.name as cropname from (select t1.*,t2.username from t_agrinfo t1 \n" +
            "inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            "inner JOIN t_crop t4 on t3.type=t4.id where IF(ISNULL(#{areaCode}),true,t3.areacode like #{areaCode})")
    public List<Agrinfo> getAgrinfoByUIdArea(@Param("areaCode") String areaCode);

    /**
     * 增加农情
     *
     * @param agrinfo
     */

    @InsertProvider(type = AgrinfoSql.class,method = "addAgrinfoSQL")
    public void addAgrinfo(@Param("agrinfo") Agrinfo agrinfo);

    /**
     * 获取农情id
     */
    @Select("SELECT LAST_INSERT_ID()")
     int getInsertAgrinfoId();


    /**
     * 增加农情图片
     * @param imageURLContent
     * @param agrInfoId
     */
    @Insert("insert into t_agrinfoimg(agrInfoId,imageURLContent) values(#{agrInfoId},replace(#{imageURLContent},'\"',''))")
     int addAgrinfoImg(@Param("agrInfoId") Integer agrInfoId,@Param("imageURLContent")String imageURLContent);

    /**
     * 获取某地区各分类在当年每个月农情上传的数量
     *
     * @return
     */
    @Select("<script>\n" +
            "SELECT t.areacode,c.name,COUNT(t.id) AS count,date_format(t.uploadTime,'%m') AS month, date_format(t.uploadTime,'%Y') AS year\n" +
            "FROM t_agrinfo t JOIN t_cropstype c ON c.id = t.type WHERE 1 = 1\n" +
            "<if test='areaCodes != null'>\n" +
            "AND t.areacode IN\n" +
            "<foreach collection='areaCodes' open='(' close=')' item='areaCode' separator=','>\n" +
            "#{areaCode}\n" +
            "</foreach>\n" +
            "</if>\n" +
            "<if test='year != null'>\n" +
            "AND date_format(t.uploadTime,'%Y') = #{year}" +
            "</if>" +
            "AND c.levels = 2" +
            "GROUP BY\n" +
            "date_format(t.uploadTime,'%Y-%m'), t.type, t.areacode\n" +
            "</script>")
    List<Agrinfo> getAgriMsgCountByArea(@Param("areaCodes") String[] areaCodes, @Param("year") String year);

    /**
     * 获取所有的农情信息
     *
     * @return
     */
    @Select("select t3.* ,t4.`name` as cropname from (select t1.*,t2.username from t_agrinfo t1 \n" +
            "Inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            "Inner JOIN t_crop t4 on t3.type=t4.id;")
    public List<Agrinfo> getAllAgrinfo();

    /**
     * 获取所有的农情评论
     *
     * @return
     */
    @Select("select * from t_agrinfocomment")
    public List<AgrinfoComment> getAllAgrinfoComment();

    /**
     * 得到农情评论根据农情ID
     *
     * @param agrInfoId
     * @return
     */
    @Select("select t1.*,t2.username from t_agrinfocomment t1 INNER JOIN t_user t2 on t2.id=t1.userId  where t1.agrInfoId=#{agrInfoId}")
    List<AgrinfoComment> getAllAgrinfoCommentByagrInfoId(@Param("agrInfoId") int agrInfoId);

    /**
     * 添加农情评论内容
     *
     * @param agrinfoComment
     * @return
     */
    @Insert("Insert into t_agrinfocomment(agrInfoId,comment,parentId,userId) " + "values(#{agrinfoComment.agrInfoId},#{agrinfoComment.comment},#{agrinfoComment.parentId},#{agrinfoComment.userId}" + ")")
    int addagrinfocomment(@Param("agrinfoComment") AgrinfoComment agrinfoComment);

    /**
     * 添加默认为未阅读的评论
     *
     * @param userid
     * @param typeid
     * @param type
     * @param comment
     * @param state
     * @return
     */
    @Insert("Insert into t_newmessage(userid,typeid,type,comment,state,uid) "
            + "values(#{userid},#{typeid},#{type},#{comment},#{state},#{uid})")
    int addNewMessage(@Param("userid") int userid, @Param("typeid") int typeid, @Param("type") int type, @Param("comment") String comment, @Param("state") int state, @Param("uid") String uid);

    /**
     * 删除农情
     *
     * @param agriId
     */
    @Delete("Delete from t_agrinfo where id=#{agriId}")
    public void delAgrinfo(@Param("agriId") int agriId);

    /**
     * 根据areacode和type得到农情
     *
     * @param areacode
     * @param type
     * @return
     */

    @Select("select * from t_agrinfo where areacode=#{areacode} and type=#{type} ")
    List<Agrinfo> getAgrinfoByAreacodeandCropid(@Param("areacode") String areacode, @Param("type") String type);

    /**
     * 根据条件得到农情
     *
     * @param type
     * @return
     */
    @Select("select t1.*,t2.username as username,t3.img_ico as cropImgIcon,t4.areaname from t_agrinfo t1  left join t_user t2 on t1.userId = t2.id left join t_area t4 on t4.areacode = t1.areacode left join t_crop t3 on t1.type in(select id from t_crop where id in(select id from t_crop where parentid = t3.id or id=t3.id) or parentid in (select id from t_crop where parentid = t3.id )) "
            + " where if((isnull(#{type}) or#{type}=''),true,t1.type in(select id from t_crop where id in(select id from t_crop where parentid = #{type} or id= #{type}) or"
            + " parentid in (select id from t_crop where parentid = #{type}  ))) and if(isnull(#{areaCode}),true,t1.areacode like CONCAT(#{areaCode},'%')) and if(isnull(#{dateTime}),true,t1.createTime like CONCAT(#{dateTime},'%'))")
    List<Agrinfo> getAgrinfos(@Param("type") String type, @Param("areaCode") String areaCode, @Param("dateTime") String dateTime);

    /**
     * 获得用户的所有农情信息
     *
     * @return
     */
    @Select("select t3.* ,t4.name as cropname from (select t1.*,t2.username,t2.showName,t2.rolecode from t_agrinfo t1 \n" +
            "inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            "inner JOIN t_crop t4 on t3.type=t4.id where IF(ISNULL(#{showName}),true,t3.showName like #{showName}) \n" +
            "and IF(ISNULL(#{areaCode}),true,t3.areaCode like #{areaCode})")
    public List<Agrinfo> searchAgrInfoByShowName(@Param("showName") String showName, @Param("areaCode") String areaCode);

    /**
     * 根据userId获取该农情的评论 点赞 分页
     *
     * @param table
     * @param userId
     * @return
     */
    @Select("select " +
            " if((select count(1) from t_agricollect t where t.userId=#{userId} and t.agrId=t1.id)>0,1,0) as isConllect" +
            "t1.id,t1.`longitude` AS lon,t1.`latitude` AS lat,t1.descript,t2.username as publisherName,t2.address as publisherAddress,t2.HeadImage as publisherHeadImage,t1.img_url as publisherAgrImg ,t1.createtime as publisherTime,t1.development as development,t3.name AS cropName " +
            " from  t_agrinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id  INNER JOIN t_crop t3 ON t1.`type`=t3.`id` " +
            "where t1.userId=#{userId} and t1.del_flg=0 ORDER BY t1.createtime desc limit #{table.startPoint},#{table.limit}")
    List<AgrAllInfo> getAgrAllInfoByUserId(@Param("table") Table table, @Param("userId") String userId);

    /**
     * 根据uareaCode获取该农情的评论 点赞 分页
     *
     * @param table
     * @param areaCode
     * @return
     */
    @Select("select" +
            "if((select count(1) from t_agricollect t where t.userId=t1.userId and t.agrId=t1.id)>0,1,0) as isConllect" +
            " t1.id,t1.`longitude` AS lon,t1.`latitude` AS lat,t1.descript,t2.username as publisherName,t2.address as publisherAddress,t2.HeadImage as publisherHeadImage,t1.img_url as publisherAgrImg ,t1.createtime as publisherTime ,t1.development as development,t3.name AS cropName " +
            " from  t_agrinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id  INNER JOIN t_crop t3 ON t1.`type`=t3.`id` " +
            "where t1.areacode like #{areaCode} and t1.del_flg=0 ORDER BY t1.createtime desc limit #{table.startPoint},#{table.limit}")
    List<AgrAllInfo> getAgrAllInfoByAreaCode(@Param("table") Table table, @Param("areaCode") String areaCode);

    /**
     * 根据userId发布农情总数
     *
     * @param userId
     * @return
     */
    @Select("select count(*)  from  t_agrinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id  INNER JOIN t_crop t3 ON t1.`type`=t3.`id` \n" +
            "where t1.userId=#{userId} and t1.del_flg=0 ORDER BY t1.createtime desc")
    int getAgrCountUserId(@Param("userId") String userId);

    /**
     * 根据areaCode查询农情总数
     *
     * @param areaCode
     * @return
     */
    @Select("select count(*)  from  t_agrinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id  INNER JOIN t_crop t3 ON t1.`type`=t3.`id` \n" +
            "where t1.areacode like #{areaCode} and t1.del_flg=0 ORDER BY t1.createtime desc")
    int getAgrCountByAreaCode(@Param("areaCode") String areaCode);

    /**
     * 该农情的点赞总数
     *
     * @param agrInfoId
     * @return
     */
    @Select("select count(*) from t_agrpraise where agrInfoId=#{agrInfoId}")
    int getAgrPraise(@Param("agrInfoId") int agrInfoId);

    /**
     * 是否对该农情点赞
     *
     * @param agrInfoId
     * @return
     */
    @Select("select isPraise from t_agrpraise  where userId=#{userId} and agrInfoId=#{agrInfoId} ")
    String isPraise(@Param("userId") String userId, @Param("agrInfoId") int agrInfoId);

    /**
     * 根据农情ID得到对应所有评论
     *
     * @param agrInfoId
     * @return
     */
    @Select("select t1.*,t2.username from t_agrinfocomment t1 inner JOIN t_user t2 on t2.id=t1.userId where t1.agrInfoId=#{agrInfoId} ")
    List<AgrinfoComment> getAgrComment(@Param("agrInfoId") int agrInfoId);

    /**
     * 内部类 编写动态sql
     *
     * @author lxs
     */
    public class AgrinfoSql {

            public String addAgrinfoSQL(Map<String, Object> map) {
                Agrinfo agrinfo = (Agrinfo) map.get("agrinfo");
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.append("insert into t_agrinfo");
                StringBuilder title = new StringBuilder();
                StringBuilder val = new StringBuilder();
                if (agrinfo.getUserId() != null) {
                    title.append("userId,");
                    val.append("'" + agrinfo.getUserId() + "',");
                }
                if (agrinfo.getFarm() != null) {
                    title.append("farm,");
                    val.append("'" + agrinfo.getFarm() + "',");
                }
                if (agrinfo.getType() != null) {
                    title.append("type,");
                    val.append("'" + agrinfo.getType() + "',");
                }
                if (agrinfo.getBreed() != null) {
                    title.append("breed,");
                    val.append("'" + agrinfo.getBreed() + "',");
                }

                if (agrinfo.getGrowthperiod() != null) {
                    title.append("growthperiod,");
                    val.append("'" + agrinfo.getGrowthperiod() + "',");
                }
                if (agrinfo.getLongitude() != null) {
                    title.append("longitude,");
                    val.append("'" + agrinfo.getLongitude() + "',");
                }
                if (agrinfo.getLatitude() != null) {
                    title.append("latitude,");
                    val.append("'" + agrinfo.getLatitude() + "',");
                }

                if (agrinfo.getAreacode() != null) {
                    title.append("areacode,");
                    val.append("'" + agrinfo.getAreacode() + "',");
                }
                if (agrinfo.getDescript() != null) {
                    title.append("descript,");
                    val.append("'" + agrinfo.getDescript() + "',");
                }

                if (agrinfo.getDevelopment() != null) {
                    title.append("development,");
                    val.append("'" + agrinfo.getDevelopment() + "',");
                }
                return   stringBuilder.append("(" + title.substring(0, title.lastIndexOf(",")) + ") values(" + val.substring(0, val.lastIndexOf(",")) + ")").toString();
            }

        //统计灾情，咨询，农情
        public String agriInfoCountSQL(Map<String, Object> map) {

            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            Agrinfo agrinfo = (Agrinfo) map.get("agrinfo");
            StringBuilder sql = new StringBuilder();
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();
            StringBuilder sql3 = new StringBuilder();
            sql.append("SELECT a.areacode areacode, a.areaname areaname, ( CASE WHEN count1 IS NULL THEN 0 ELSE count1 END ) disaterCount,\n" +
                    "                     ( CASE WHEN count2 IS NULL THEN 0 ELSE count2 END ) agrinfoCount, \n" +
                    "                    ( CASE WHEN count3 IS NULL THEN 0 ELSE count3 END ) advisoryInfoCount FROM t_area a \n" +
                    "                    LEFT JOIN ( SELECT areacode, COUNT(id) count1 FROM `t_disaster` t where del_flg=0  ");

            sql1.append(" GROUP BY areacode ) b ON a.areacode = b.areacode LEFT JOIN ( SELECT areacode, COUNT(id) count2 FROM t_agrinfo t1 where 1=1 ");

            sql2.append("   GROUP BY areacode ) c ON a.areacode = c.areacode \n" +
                    "                    LEFT JOIN ( SELECT areacode, COUNT(id) count3 FROM t_advisoryinfo t2 where del_flg=0 ");

            sql3.append(" GROUP BY areacode ) d ON a.areacode = d.areacode where 1=1 and a.areacode != '50' ");

            if (agrinfo.getAreacode() != null && !"".equals(agrinfo.getAreacode())) {
                sql3.append(" and a.areacode like '" + agrinfo.getAreacode() + "%'");
            }

            if(agrinfo.getStartTime()!=null&&!"".equals(agrinfo.getStartTime())){
                sql.append(" and DATE_FORMAT(t.CREATEtime,'%Y-%m-%d') >= '"+agrinfo.getStartTime().trim()+"'");
                sql1.append(" and DATE_FORMAT(t1.CREATEtime,'%Y-%m-%d') >='"+agrinfo.getStartTime().trim()+"'");
                sql2.append(" and DATE_FORMAT(t2.uploadtime,'%Y-%m-%d') >= '"+agrinfo.getStartTime().trim()+"'");

            }
            if(agrinfo.getEndTime()!=null&&!"".equals(agrinfo.getEndTime())){
                sql.append(" and DATE_FORMAT(t.CREATEtime,'%Y-%m-%d') <= '"+agrinfo.getEndTime().trim()+"'");
                sql1.append(" and DATE_FORMAT(t1.CREATEtime,'%Y-%m-%d') <= '"+agrinfo.getEndTime().trim()+"'");
                sql2.append(" and DATE_FORMAT(t2.uploadtime,'%Y-%m-%d') <= '"+agrinfo.getEndTime().trim()+"'");
            }

            sql3.append(" group by a.areacode, a.areaname ");
            sql3.append(" limit " + table.getStartPoint() + "," + table.getLimit());
            sql3.append(";");
            return sql.toString()+sql1.toString()+sql2.toString()+sql3.toString();
        }

        //统计用户，农田
        public String farmCountSQL(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            Agrinfo agrinfo = (Agrinfo) map.get("agrinfo");
            StringBuilder sql = new StringBuilder();
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();
            StringBuilder sql3 = new StringBuilder();
            sql.append("SELECT\n" +
                    "\ta.areacode areacode,\n" +
                    "\ta.areaname areaname,\n" +
                    "\t(\n" +
                    "\t\tCASE  when countUser IS NULL THEN\n" +
                    "\t\t\t0 ELSE countUser END\n" +
                    "\t) userCount,\n" +
                    "\t(\n" +
                    "\t\tCASE WHEN farmnameCount IS NULL THEN\n" +
                    "\t\t\t0 ELSE farmnameCount END\n" +
                    "\t) farmnameCount\n" +
                    "FROM\n" +
                    "\tt_area a\n" +
                    "LEFT JOIN (\n" +
                    "\tSELECT\n" +
                    "\t\tareacode,\n" +
                    "\t\tcount(1) countUser\n" +
                    "\tFROM\n" +
                    "\t\tt_user t1\n" +
                    "\tWHERE\n" +
                    "\t\tdel_flg = 0  ");
            sql1.append("GROUP BY\n" +
                    "\t\tareacode\n" +
                    ") b ON a.areacode = b.areacode\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "\t\tt. CODE areacode,\n" +
                    "\t\tcount(1) farmnameCount\n" +
                    "\tFROM\n" +
                    "\t\tt_farmland t\n" +
                    "\tWHERE\n" +
                    "\t\tt.del_flg = 0 ");
            sql2.append("\tGROUP BY\n" +
                    "\t\tt. CODE\n" +
                    ") c ON a.areacode = c.areacode\n" +
                    "WHERE\n" +
                    "\t1 = 1  and a.areacode != '50' ");
            if (agrinfo.getAreacode() != null && !"".equals(agrinfo.getAreacode())) {
                sql3.append("and a.areacode LIKE '" + agrinfo.getAreacode() + "%'");
            }
            if(agrinfo.getStartTime()!=null&&!"".equals(agrinfo.getStartTime())){
                sql.append(" and DATE_FORMAT(t1.CREATEtime,'%Y-%m-%d') >= '"+agrinfo.getStartTime().trim()+"'");
                sql1.append(" and DATE_FORMAT(t.CREATEtime,'%Y-%m-%d') >='"+agrinfo.getStartTime().trim()+"'");
            }
            if(agrinfo.getEndTime()!=null&&!"".equals(agrinfo.getEndTime())){
                sql.append(" and DATE_FORMAT(t1.CREATEtime,'%Y-%m-%d') <= '"+agrinfo.getEndTime().trim()+"'");
                sql1.append(" and DATE_FORMAT(t.CREATEtime,'%Y-%m-%d') <= '"+agrinfo.getEndTime().trim()+"'");
            }
            sql3.append(" group by a.areacode, a.areaname ");
            sql3.append(" limit " + table.getStartPoint() + "," + table.getLimit());
            sql3.append(";");
            return sql.toString()+sql1.toString()+sql2.toString()+sql3.toString();
        }

        //按照角色统计农田1
        public String roleFarmCountSQL(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            Agrinfo agrinfo = (Agrinfo) map.get("agrinfo");
            StringBuilder sql = new StringBuilder();
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();
            sql.append("select t4.areaname,t3.name rolename,count(t2.id) as userCount from t_user t1\n" +
                    "inner join t_user_role t2 on t1.id = t2.userid\n" +
                    "inner join t_role t3 on t2.roleid = t3.id\n" +
                    "inner join t_area t4 on t1.areaCode = t4.areacode\n" +
                    "where t1.del_flg = 0 and t1.areaCode!='50' ");
            if (agrinfo.getAreacode() != null && !"".equals(agrinfo.getAreacode())) {
                sql1.append(" and t1.areaCode like '" + agrinfo.getAreacode().trim() + "%'");
            }
            sql2.append("group by t2.roleid,t4.areacode\n" +
                    "order by t4.areacode,t2.roleid ");
            sql2.append(" limit " + table.getStartPoint() + "," + table.getLimit());
            sql2.append(";");
            return sql.toString()+sql1.toString()+sql2.toString();
        }

        public String areaCountSql(Map<String, Object> map){
           String code =(String) map.get("areacode");
            StringBuilder sql = new StringBuilder();
            sql.append("select count(1) from t_area where 1=1 and ");
            if(code !=null&&!"".equals(code)){
                sql.append("areacode like '"+code+"%';");
            }
            return sql.toString();
        }
    }
}
